import paramiko as pm
import pymysql

mainDb = pymysql.connect(host='localhost', port=23306, user='root', password='Pr0dkcd_', db="kcdoc")
db_cursor = mainDb.cursor()

ssh_client1 = pm.SSHClient()
ssh_client1.set_missing_host_key_policy(pm.AutoAddPolicy())
ssh_client1.connect('127.0.0.1', port=22022, username='root', password='Kc@123456')
mainFtp = ssh_client1.open_sftp()

if __name__ == '__main__':
    sql = """
            SELECT
            pp.production_code AS planCode,
            pp.chartNo,
            pp.production_plan_name AS planChartNo,
            pp.material_number AS materialNumber,
            sat.absolute_path 
        FROM
            v_kc_production_plan pp
            INNER JOIN v_kc_process_trajectory kppp ON pp.id = kppp.production_plan_id
            LEFT JOIN kc_process_att_relation kpar ON kpar.trid = kppp.id
            LEFT JOIN kc_manufacturing_process_dir kmpd ON kmpd.id = kpar.dirid
            LEFT JOIN sys_att_t sat ON sat.id = kpar.attid 
        WHERE
            LEFT ( pp.production_code, '2' )= 'JF' 
            AND kppp.process_name = '签署交货单' 
            AND kppp.state = 2 
            AND pp.material_number IN (
                '0.100.005.0044',
                '0.100.005.0032',
            '0.100.005.0016',
            '0.100.005.0000')
        """
    db_cursor.execute(sql)
    main_info = db_cursor.fetchall()
    for e in main_info:
        split = e[4].split(".")
        file_name = e[0] + e[2] + e[3] + '.' + split[1]
        name = 'C:\\Users\\asus\\Desktop\\709\\file\\JFPlan\\' + file_name

        file_path = e[4]
        try:
            file_attr = mainFtp.stat(file_path)
            mainFtp.get(file_path, name)
        except FileNotFoundError:
            print("文件不存在:" + e[4])